package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import dto.SubjectDto;

public class SubjectDao {

	public String insertSubject(SubjectDto subject) {

		if (subject == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO subject "
				+ "(subject_id, subject_name, teadcher_user_id) "
				+ "VALUES (?,?,?)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, subject.getSubjectId());
			preparedStatement.setString(2, subject.getSubjectName());
			preparedStatement.setString(3, subject.getTeacherId());

			preparedStatement.execute();
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public String deleteSubject(String subjectId) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "DELETE FROM subject WHERE subject_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, subjectId);

			preparedStatement.execute();
			response = "Deleted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Delete failed! " + e.getMessage();
		}

		return response;
	}

	public String updateSubject(SubjectDto subject) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "UPDATE subject SET subject_name = ?, teadcher_user_id = ?"
				+ " WHERE subject_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);

			preparedStatement.setString(1, subject.getSubjectName());
			preparedStatement.setString(2, subject.getTeacherId());
			preparedStatement.setString(3, subject.getSubjectId());

			preparedStatement.execute();
			response = "Updated successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Update failed! " + e.getMessage();
		}

		return response;
	}

	public SubjectDto getSubjectById(String subjectId) {
		SubjectDto subject = null;

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM subject WHERE subject_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, subjectId);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				subject = new SubjectDto();
				subject.setSubjectId(resultSet.getString("subject_id"));
				subject.setSubjectName(resultSet.getString("subject_name"));
				subject.setTeacherId(resultSet.getString("teadcher_user_id"));
			}

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return subject;
	}
	
	public SubjectDto getSubjectByName(String subjectName) {
		SubjectDto subject = null;

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM subject WHERE subject_name = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, subjectName);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				subject = new SubjectDto();
				subject.setSubjectId(resultSet.getString("subject_id"));
				subject.setSubjectName(resultSet.getString("subject_name"));
				subject.setTeacherId(resultSet.getString("teadcher_user_id"));
			}

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return subject;
	}

	public ArrayList<SubjectDto> getAllSubject() {

		ArrayList<SubjectDto> listSubject = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM subject  order by subject_name";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				SubjectDto subject = new SubjectDto();
				subject.setSubjectId(resultSet.getString("subject_id"));
				subject.setSubjectName(resultSet.getString("subject_name"));
				subject.setTeacherId(resultSet.getString("teadcher_user_id"));
				listSubject.add(subject);
			}

			statement.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return listSubject;
	}

}
